<?php

namespace App\Http\Controllers;

use App\Http\Utils\Integral\Manual;
use App\Http\Utils\Integral\Order;
use App\Http\Utils\Integral\Time;
use App\Http\Utils\Response;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class IntegralController extends Controller
{
    /**
     * @var int
     */
    protected $page_size = 3;

    const INTEGRAL_NOT_FOUND_ROLES = '该积分规则不存在';
    const INTEGRAL_NOT_FOUND_ROLES_HISTORY = '该商户积分规则历史不存在';
    const INTEGRAL_NOT_FOUND_LUCK_TASK = '该商户还没有幸运红包规则结算详情';
    const INTEGRAL_NOT_FOUND_DAY_TASK = '该商户还没有天天红包规则结算详情';
    const INTEGRAL_NOT_FOUND_MANUAL_TASK = '该商户还没有手动红包规则结算详情';
    const INTEGRAL_NOT_FOUND_ORDER = '该商户还没有线上或线下结算详情';
    const INTEGRAL_SHOP_NOT_FOUND_ROLES = '该商户(用户)没有该积分规则';
    const INTEGRAL_NOT_ENOUGH = '该商户(用户)没有足够的积分';
    const INTEGRAL_ALREADY_HAS_LUCK_ROLES = '已经创建了幸运积分红包规则';
    const INTEGRAL_ALREADY_SHOP_ACCOUNT = '改商户已经有积分账户';
    const INTEGRAL_NOT_FOUND_FLOW = '该商户(用户)没有流水详情';
    const INTEGRAL_NOT_ALLOW_REFUND = '该消费不能退款';
    const INTEGRAL_ORDER_ALREADY_FINISH = '该笔订单已经完成';

    /**
     * @param Request $request
     * @return Response
     * @throws \Exception
     */
    public function create(Request $request)
    {
        $this->validate($request, [
            'shop_id'           => 'required|alpha_num',
            'type'              => 'required|in:day,luck',
            'disable'           => 'required|in:true,false',
            'day_target'        => 'required_if:type,day|in:every,old,new',
            'day_type'          => 'required_if:type,day|in:single,cumulative',
            'day_satisfy'       => 'required_if:type,day|integer|min:1',
            'day_return'        => 'required_if:type,day|integer|min:1',
            'day_every'         => 'required_if:type,day|integer|min:1',
            'day_overly'        => 'required_if:type,day|in:true,false',
            'luck_consumption'  => 'required_if:type,luck|in:true,false',
            'luck_day_off'      => 'required_if:type,luck|in:true,false',
            'luck_count'        => 'required_if:type,luck|integer|min:0',
            'luck_ratio'        => 'required_if:type,luck|numeric|min:0.01',
            'luck_popularize'   => 'array',
            'luck_distribution' => 'array',
        ]);
        switch ($request->input('type')) {
            case 'day':
                $data = $request->only([
                    'shop_id',
                    'type',
                    'disable',
                    'day_target',
                    'day_type',
                    'day_satisfy',
                    'day_return',
                    'day_every',
                    'day_overly',
                ]);
                break;
            case 'luck':
                $luck = DB::table('shop_roles')->where('shop_id', $request->input('shop_id'))
                    ->where('type', 'luck')->first(['id']);
                if (!is_null($luck)) {
                    throw new \Exception(self::INTEGRAL_ALREADY_HAS_LUCK_ROLES, 400);
                }
                $data = $request->only([
                    'shop_id',
                    'type',
                    'disable',
                    'luck_consumption',
                    'luck_day_off',
                    'luck_count',
                    'luck_ratio',
                ]);
                $data['luck_popularize'] = json_encode($request->input('luck_popularize', []));
                $data['luck_distribution'] = json_encode($request->input('luck_distribution', []));
                break;
            default:
                throw new \Exception('');
        }
        $data['create_time'] = Time::current();
        $data['update_time'] = Time::current();
        $id = DB::table('shop_roles')->insertGetId($data);
        return $this->json($id);
    }

    /**
     * @param int $id
     * @return Response
     * @throws \Exception
     */
    public function delete($id)
    {
        $role = DB::table('shop_roles')->where(['id' => $id])->first(['id']);
        if (is_null($role)) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_ROLES, 404);
        }
        DB::table('shop_roles')->where(['id' => $id,])->delete();
        return $this->json(null);
    }

    /**
     * @param Request $request
     * @param int $id
     * @return Response
     * @throws \Exception
     */
    public function edit(Request $request, $id)
    {
        $role = DB::table('shop_roles')->where(['id' => $id,])->first();
        if (is_null($role)) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_ROLES, 404);
        }
        $this->validate($request, [
            'type'              => 'required|in:day,luck',
            'disable'           => 'in:true,false',
            'day_target'        => 'in:every,old,new',
            'day_type'          => 'in:single,cumulative',
            'day_satisfy'       => 'integer|min:1',
            'day_return'        => 'integer|min:1',
            'day_every'         => 'integer|min:1',
            'day_overly'        => 'in:true,false',
            'luck_consumption'  => 'in:true,false',
            'luck_day_off'      => 'in:true,false',
            'luck_count'        => 'integer|min:0',
            'luck_ratio'        => 'numeric|min:0.01',
            'luck_popularize'   => 'array',
            'luck_distribution' => 'array',
        ]);
        switch ($request->input('type')) {
            case 'day':
                $data = $request->only([
                    'type',
                    'disable',
                    'day_target',
                    'day_type',
                    'day_satisfy',
                    'day_return',
                    'day_every',
                    'day_overly',
                ]);
                break;
            case 'luck':
                $data = $request->only([
                    'type',
                    'disable',
                    'luck_consumption',
                    'luck_day_off',
                    'luck_count',
                    'luck_ratio',
                ]);
                if ($request->has('luck_popularize')) {
                    $data['luck_popularize'] = json_encode($request->input('luck_popularize', []));
                }
                if ($request->has('luck_distribution')) {
                    $data['luck_distribution'] = json_encode($request->input('luck_distribution', []));
                }
                break;
            default:
                throw new \Exception('');
        }
        $data['update_time'] = Time::current();
        DB::transaction(function () use ($id, $data, $role) {
            DB::table('shop_roles')->where(['id' => $id])->update($data);
            DB::table('role_history')->insert([
                'shop_id'     => $role->shop_id,
                'role_id'     => $id,
                'pistolgraph' => json_encode($role),
                'create_time' => Time::current(),
                'update_time' => Time::current(),
            ]);
        });
        return $this->json($data);
    }

    /**
     * @param Request $request
     * @param int $shop_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function roleHistory(Request $request, $shop_id, $page)
    {
        $this->validate($request, [
            'page_size' => 'integer|min:1',
        ]);
        $base = DB::table('role_history')->where(['shop_id' => $shop_id,]);
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_ROLES_HISTORY, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $history = $base->orderBy('id', 'desc')->offset(($page - 1) * $page_size)
            ->limit($page_size)->get()->map(function ($history) {
                $history->roles = json_decode($history->pistolgraph);
                unset($history->pistolgraph);
                return $history;
            });
        return $this->json([
            'page_size'    => (int)$page_size,
            'count'        => (int)$count,
            'total_page'   => (int)$totalPage,
            'current_page' => (int)$page,
            'history'      => $history,
        ]);
    }

    /**
     * @param int $id
     * @return Response
     * @throws \Exception
     */
    public function index($id)
    {
        $role = DB::table('shop_roles')->where(['id' => $id,])->first();
        if (is_null($role)) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_ROLES, 404);
        }
        return $this->json($role);
    }

    /**
     * @param int $shop_id
     * @return Response
     * @throws \Exception
     */
    public function luck($shop_id)
    {
        $role = DB::table('shop_roles')->where(['shop_id' => $shop_id, 'type' => 'luck'])->first();
        if (is_null($role)) {
            throw new \Exception(self::INTEGRAL_SHOP_NOT_FOUND_ROLES, 404);
        }
        return $this->json($role);
    }

    /**
     * @param Request $request
     * @param int $shop_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function lists(Request $request, $shop_id, $page)
    {
        $this->validate($request, [
            'page_size' => 'integer|min:1',
        ]);
        $base = DB::table('shop_roles')->where(['shop_id' => $shop_id, 'type' => 'day']);
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_SHOP_NOT_FOUND_ROLES, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $role = $base->orderBy('id', 'desc')->offset(($page - 1) * $page_size)->limit($page_size)->get();
        return $this->json([
            'page_size'    => (int)$page_size,
            'count'        => (int)$count,
            'total_page'   => (int)$totalPage,
            'current_page' => (int)$page,
            'role'         => $role,
        ]);
    }

    /*----------------------------------------------------------------------*/

    /**
     * TODO
     * @param Request $request
     * @param int $shop_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function taskLuck(Request $request, $shop_id, $page)
    {
        $this->validate($request, [
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        $start = $request->input('start', null);
        $end = $request->input('end', null);
        $search = $request->input('search', null);
        $query = DB::query()
            ->select(DB::raw("task.id,task.user_id,task.enabled"))
            ->addSelect(DB::raw("CAST(JSON_EXTRACT(task.pistolgraph,'$.luck_ratio') as decimal(11,4)) as luck_ratio"))
            ->addSelect(DB::raw("SUM(task.grant_integral) as total_integral"))
            ->addSelect(DB::raw("GROUP_CONCAT(task.id) as task_ids"))
            ->addSelect(DB::raw("SUM(JSON_EXTRACT(task.pistolgraph,'$.luck_count')) - SUM(task.count) as total_count"))
            ->from('task')
            ->where('task.shop_id', $shop_id)
            ->where('task.type', 'luck')
            ->when($start, function ($query) use ($start) {
                $query->where('task.create_time', '>=', $start);
            })->when($end, function ($query) use ($end) {
                $query->where('task.create_time', '<=', $end);
            })
            ->groupBy('task.user_id', 'task.shop_id')
            ->orderBy('task.id', 'desc');
        $query = DB::table(DB::raw(sprintf("(%s) temp", $query->toSql())))
            ->setBindings($query->getBindings())
            ->select([
                'temp.*',
            ])->when(true, function ($query) {
                $userDatabase = env('DB_DATABASE_USER', '');
                $query->addSelect(DB::raw("CASE LEFT(`temp`.`user_id`, 1)
                WHEN 'v' THEN (SELECT `nickname` FROM {$userDatabase}`wx_user` WHERE `id` = substring(`temp`.`user_id`, 2))
                WHEN 'a' THEN (SELECT nickname FROM {$userDatabase}`alipay_user` WHERE `id` = substring(`temp`.`user_id`, 2))
                ELSE (SELECT nickname FROM {$userDatabase}`user_info`  WHERE `user_id` = `temp`.`user_id`)
                END as nickname"));
                $query->addSelect(DB::raw("CASE LEFT(`temp`.`user_id`, 1)
                WHEN 'v' THEN '匿名用户无手机号'
                WHEN 'a' THEN '匿名用户无手机号'
                ELSE (SELECT mobile FROM {$userDatabase}`user`  WHERE `id` = `temp`.`user_id`)
                END as mobile"));
            });
        $base = DB::table(DB::raw(sprintf("(%s) temp", $query->toSql())))
            ->setBindings($query->getBindings())
            ->when($search, function ($query) use ($search) {
                $query->whereRaw('CONCAT(mobile,nickname) LIKE ?', "%{$search}%");
            });
        $count = $base->count('temp.id');
        $sum = $base->sum('temp.total_integral');
        $times = $base->sum('temp.total_count');
        $page_size = $request->input('page_size', $this->page_size);
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_LUCK_TASK, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $task = $base->orderByDesc('temp.id')
            ->offset(($page - 1) * $page_size)
            ->limit($page_size)->get()
            ->map(function ($task) {
                $task_ids = explode(',', $task->task_ids);
                $task->details = DB::table('user_flow')
                    ->select([
                        'user_flow.integral', 'user_flow.create_time', 'user_flow.task_id',
                    ])->addSelect([
                        'orders.user_id', 'orders.order_num', 'orders.cash_ratio', 'orders.inte_ratio',
                        'orders.amount', 'orders.total', 'orders.use_integral', 'orders.integral as order_integral',
                    ])->leftJoin('orders', 'orders.order_num', '=', 'user_flow.order_num')
                    ->when(true, function ($query) {
                        $userDatabase = env('DB_DATABASE_USER', '');
                        $query->addSelect(DB::raw("CASE LEFT(`orders`.`user_id`, 1)
                            WHEN 'v' THEN (SELECT `nickname` FROM {$userDatabase}`wx_user` WHERE `id` = substring(`orders`.`user_id`, 2))
                            WHEN 'a' THEN (SELECT nickname FROM {$userDatabase}`alipay_user` WHERE `id` = substring(`orders`.`user_id`, 2))
                            ELSE (SELECT nickname FROM {$userDatabase}`user_info`  WHERE `user_id` = `orders`.`user_id`)
                            END as nickname"));
                        $query->addSelect(DB::raw("CASE LEFT(`orders`.`user_id`, 1)
                            WHEN 'v' THEN '匿名用户无手机号'
                            WHEN 'a' THEN '匿名用户无手机号'
                            ELSE (SELECT mobile FROM {$userDatabase}`user`  WHERE `id` = `orders`.`user_id`)
                            END as mobile"));
                    })
                    ->where('user_flow.status', 'success')
                    ->whereIn('user_flow.task_id', $task_ids)
                    ->orderBy('user_flow.create_time', 'asc')
                    ->get();
                return $task;
            });
        return $this->json([
            'page_size'      => (int)$page_size,
            'count'          => (int)$count,
            'total_page'     => (int)$totalPage,
            'current_page'   => (int)$page,
            'total_count'    => $count,
            'total_integral' => $sum,
            'total_times'    => $times,
            'tasks'          => $task,
        ]);
    }

    /**
     * @param Request $request
     * @param int $shop_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function taskDay(Request $request, $shop_id, $page)
    {
        $this->validate($request, [
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        $start = $request->input('start', null);
        $end = $request->input('end', null);
        $query = DB::table('task')
            ->select([
                'task.id', 'task.user_id', 'task.shop_id',
            ])
            //->addSelect(DB::raw("GROUP_CONCAT(task.id) as fid"))
            ->addSelect(DB::raw("SUM(task.count) as count"))
            ->addSelect(DB::raw("SUM(task.grant_integral) as grant_integral"))
            ->addSelect(DB::raw("SUM(task.unissued_integral) as unissued_integral"))
            ->addSelect(DB::raw("SUM(JSON_EXTRACT(task.pistolgraph,'$.day_return')) as day_return"))
            //->addSelect(DB::raw("SUM(JSON_EXTRACT(task.pistolgraph,'$.day_every')) as day_every"))
            ->where([
                'task.shop_id' => $shop_id,
                'task.type'    => 'day',
            ])
            ->when(true, function ($query) {
                $userDatabase = env('DB_DATABASE_USER', '');
                $query->addSelect(DB::raw("CASE LEFT(`task`.`user_id`, 1)
                WHEN 'v' THEN (SELECT `nickname` FROM {$userDatabase}`wx_user` WHERE `id` = substring(`task`.`user_id`, 2))
                WHEN 'a' THEN (SELECT nickname FROM {$userDatabase}`alipay_user` WHERE `id` = substring(`task`.`user_id`, 2))
                ELSE (SELECT nickname FROM {$userDatabase}`user_info`  WHERE `user_id` = `task`.`user_id`)
                END as nickname"));
                $query->addSelect(DB::raw("CASE LEFT(`task`.`user_id`, 1)
                WHEN 'v' THEN '匿名用户无手机号'
                WHEN 'a' THEN '匿名用户无手机号'
                ELSE (SELECT mobile FROM {$userDatabase}`user`  WHERE `id` = `task`.`user_id`)
                END as mobile"));
            })->when($start, function ($query) use ($start) {
                $query->where('task.create_time', '>=', $start);
            })->when($end, function ($query) use ($end) {
                $query->where('task.create_time', '<=', $end);
            })->groupBy('task.user_id');
        $search = $request->input('search', null);
        $base = DB::table(DB::raw(sprintf("(%s) temp", $query->toSql())))
            ->setBindings($query->getBindings())
            ->addSelect([
                'temp.*', 'user.day_enabled as enabled'
            ])->leftJoin('user', function ($join) {
                $join->on('user.user_id', '=', 'temp.user_id')
                    ->on('user.shop_id', '=', 'temp.shop_id');
            })->when($search, function ($query) use ($search) {
                $query->whereRaw('CONCAT(mobile,nickname) LIKE ?', "%{$search}%");
            });
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('temp.id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_DAY_TASK, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $task = $base->orderBy('temp.id', 'desc')->offset(($page - 1) * $page_size)->limit($page_size)->get()
            ->map(function ($value) {
                $value->day_every = DB::table('task')
                    ->where('user_id', $value->user_id)
                    ->where('shop_id', $value->shop_id)
                    ->where('type', 'day')
                    ->where('count', '>', 0)
                    ->sum('pistolgraph->day_every');
                return $value;
            });
        $day_every = DB::table('task')
            ->where('shop_id', $shop_id)
            ->where('type', 'day')
            ->where('count', '>', 0)
            ->sum('pistolgraph->day_every');
        return $this->json([
            'page_size'    => (int)$page_size,
            'count'        => (int)$count,
            'total_page'   => (int)$totalPage,
            'current_page' => (int)$page,
            'day_every'    => (int)$day_every,
            'tasks'        => $task,
        ]);
    }

    /**
     * @param Request $request
     * @param int $shop_id
     * @param string $user_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function taskDayByUser(Request $request, $shop_id, $user_id, $page)
    {
        $this->validate($request, [
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        $start = $request->input('start', null);
        $end = $request->input('end', null);
        $search = $request->input('search', null);
        $base = DB::table('task')
            ->select([
                'task.id',
                'task.user_id',
                'task.enabled',
                DB::raw("CAST(JSON_EXTRACT(task.pistolgraph,'$.day_return') as SIGNED) as day_return"),
                DB::raw("CAST(JSON_EXTRACT(task.pistolgraph,'$.day_every') as SIGNED) as day_every"),
                'task.count as ready_grant',
                'task.grant_integral',
                'task.unissued_integral',
                'task.create_time',
            ])->addSelect(DB::raw('orders.order_num,orders.total,orders.amount'))
            ->leftJoin('orders', 'orders.order_num', '=', 'task.order_num')
            ->addSelect(['shop_roles.day_type', 'shop_roles.day_target'])
            ->leftJoin('shop_roles', 'shop_roles.id', '=', 'task.pistolgraph->id')
            ->where([
                'task.shop_id' => $shop_id,
                'task.user_id' => $user_id,
                'task.type'    => 'day',
            ])->when($start, function ($query) use ($start) {
                $query->where('task.create_time', '>=', $start);
            })->when($end, function ($query) use ($end) {
                $query->where('task.create_time', '<=', $end);
            })->when($search, function ($query) use ($search) {
                $query->whereRaw('task.order_num LIKE ?', "%{$search}%");
            });
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('task.id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_DAY_TASK, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $task = $base->orderBy('task.id', 'desc')
            ->offset(($page - 1) * $page_size)->limit($page_size)->get()
            ->map(function ($task) {
                $task->days = ceil($task->grant_integral / $task->day_every);
                if ($task->ready_grant == 0) {
                    $task->day_every = 0;
                }
                return $task;
            });
        return $this->json([
            'page_size'    => (int)$page_size,
            'count'        => (int)$count,
            'total_page'   => (int)$totalPage,
            'current_page' => (int)$page,
            'tasks'        => $task,
        ]);
    }

    /**
     * @param Request $request
     * @param $shop_id
     * @param $page
     * @return Response
     * @throws \Exception
     */
    public function taskManual(Request $request, $shop_id, $page)
    {
        $this->validate($request, [
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        $start = $request->input('start', null);
        $end = $request->input('end', null);
        $query = DB::table('task')
            ->select([
                'task.id', 'task.user_id', 'task.shop_id',
            ])
            ->addSelect(DB::raw("SUM(task.count) as count"))
            ->addSelect(DB::raw("SUM(task.grant_integral) as grant_integral"))
            ->addSelect(DB::raw("SUM(JSON_EXTRACT(task.pistolgraph,'$.day_return')) as day_return"))
            //->addSelect(DB::raw("SUM(JSON_EXTRACT(task.pistolgraph,'$.day_every')) as day_every"))
            ->where([
                'task.shop_id' => $shop_id,
                'task.type'    => 'manual',
            ])->when(true, function ($query) {
                $userDatabase = env('DB_DATABASE_USER', '');
                $query->addSelect(DB::raw("CASE LEFT(`task`.`user_id`, 1)
                WHEN 'v' THEN (SELECT `nickname` FROM {$userDatabase}`wx_user` WHERE `id` = substring(`task`.`user_id`, 2))
                WHEN 'a' THEN (SELECT nickname FROM {$userDatabase}`alipay_user` WHERE `id` = substring(`task`.`user_id`, 2))
                ELSE (SELECT nickname FROM {$userDatabase}`user_info`  WHERE `user_id` = `task`.`user_id`)
                END as nickname"));
                $query->addSelect(DB::raw("CASE LEFT(`task`.`user_id`, 1)
                WHEN 'v' THEN '匿名用户无手机号'
                WHEN 'a' THEN '匿名用户无手机号'
                ELSE (SELECT mobile FROM {$userDatabase}`user`  WHERE `id` = `task`.`user_id`)
                END as mobile"));
            })->when($start, function ($query) use ($start) {
                $query->where('task.create_time', '>=', $start);
            })->when($end, function ($query) use ($end) {
                $query->where('task.create_time', '<=', $end);
            })->groupBy('task.user_id');
        $search = $request->input('search', null);
        $order = DB::table(env('DB_DATABASE_ORDER', '') . 'oc_trade as trade')
            ->select([
                'trade.user_id', 'bill.shop_id',
                DB::raw('SUM(bill.pay_amount) as total_amount'),
                DB::raw('SUM(bill.pay_integral) as total_integral'),
            ])
            ->leftJoin(env('DB_DATABASE_ORDER', '') . 'oc_bill as bill', function ($join) {
                $join->on('bill.trade_id', '=', 'trade.id');
            })->groupBy('trade.user_id', 'bill.shop_id');
        $min = $request->input('min', null);
        $max = $request->input('max', null);
        $base = DB::table(DB::raw(sprintf("(%s) temp", $query->toSql())))
            ->setBindings($query->getBindings())
            ->addSelect([
                'temp.*', 'user.mal_enabled as enabled'
            ])->leftJoin('user', function ($join) {
                $join->on('user.user_id', '=', 'temp.user_id')->on('user.shop_id', '=', 'temp.shop_id');
            })->addSelect([
                'tb.total_amount', 'tb.total_integral'
            ])->leftJoin(DB::raw(sprintf("(%s) as tb", $order->toSql())), function ($join) {
                $join->on('tb.user_id', '=', 'temp.user_id')->on('tb.shop_id', '=', 'temp.shop_id');
            })->when($min, function ($query) use ($min) {
                $query->where('tb.total_amount', '>=', $min);
            })->when($max, function ($query) use ($max) {
                $query->where('tb.total_amount', '<=', $max);
            })->when($search, function ($query) use ($search) {
                $query->whereRaw('CONCAT(mobile,nickname) LIKE ?', "%{$search}%");
            });
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('temp.id');
        $day_return = $base->sum('temp.day_return');
        $need_grant = $base->sum('temp.count');
        $grant_integral = $base->sum('temp.grant_integral');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_MANUAL_TASK, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $task = $base->orderBy('temp.id', 'desc')->offset(($page - 1) * $page_size)->limit($page_size)->get()
            ->map(function ($value) {
                $value->day_every = DB::table('task')
                    ->where('user_id', $value->user_id)
                    ->where('shop_id', $value->shop_id)
                    ->where('type', 'manual')
                    ->where('count', '>', 0)
                    ->sum('pistolgraph->day_every');
                return $value;
            });
        $day_every = DB::table('task')
            ->where('shop_id', $shop_id)
            ->where('type', 'manual')
            ->where('count', '>', 0)
            ->sum('pistolgraph->day_every');

        return $this->json([
            'page_size'      => (int)$page_size,
            'count'          => (int)$count,
            'total_page'     => (int)$totalPage,
            'current_page'   => (int)$page,
            'day_return'     => (int)$day_return,
            'day_every'      => (int)$day_every,
            'need_grant'     => (int)$need_grant,
            'grant_integral' => (int)$grant_integral,
            'tasks'          => $task,
        ]);
    }

    /**
     * @param Request $request
     * @param string $shop_id
     * @param string $user_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function taskManualByUser(Request $request, $shop_id, $user_id, $page)
    {
        $this->validate($request, [
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        $start = $request->input('start', null);
        $end = $request->input('end', null);
        $base = DB::table('task')
            ->select([
                'task.id',
                'task.user_id',
                'task.enabled',
                DB::raw("CAST(JSON_EXTRACT(task.pistolgraph,'$.day_return') as SIGNED) as day_return"),
                DB::raw("CAST(JSON_EXTRACT(task.pistolgraph,'$.day_every') as SIGNED) as day_every"),
                'task.count as ready_grant',
                'task.grant_integral',
                'task.create_time',
            ])->where([
                'task.shop_id' => $shop_id,
                'task.user_id' => $user_id,
                'task.type'    => 'manual',
            ])->when($start, function ($query) use ($start) {
                $query->where('task.create_time', '>=', $start);
            })->when($end, function ($query) use ($end) {
                $query->where('task.create_time', '<=', $end);
            });

        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('task.id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_MANUAL_TASK, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $task = $base->orderBy('task.id', 'desc')
            ->offset(($page - 1) * $page_size)->limit($page_size)->get()
            ->map(function ($task) {
                $task->days = ceil($task->grant_integral / $task->day_every);
                if ($task->ready_grant == 0) {
                    $task->day_every = 0;
                }
                return $task;
            });
        return $this->json([
            'page_size'    => (int)$page_size,
            'count'        => (int)$count,
            'total_page'   => (int)$totalPage,
            'current_page' => (int)$page,
            'tasks'        => $task,
        ]);
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param Request $request
     * @param int $shop_id
     * @return Response
     */
    public function shopTotal(Request $request, $shop_id)
    {
        $surplus = DB::table('shop')->where(['shop_id' => $shop_id,])->value('total_amount');
        $total = DB::table('shop_flow')->where(['shop_id' => $shop_id,])->where('integral', '>', 0)->sum('integral');
        $grant = DB::table('shop_flow')->where(['shop_id' => $shop_id,])->where('integral', '<', 0)->sum('integral');
        return $this->json([
            'total'   => (float)$total,
            'surplus' => (float)$surplus,
            'grant'   => (float)$grant,
        ]);
    }

    /**
     * @param Request $request
     * @param int $shop_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function shopFlow(Request $request, $shop_id, $page)
    {
        $this->validate($request, [
            'type'      => 'in:in,out',
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        $start = $request->input('start', null);
        $end = $request->input('end', null);
        $type = $request->input('type', null);
        $query = DB::table('shop_flow')
            ->select(['shop_flow.*'])
            ->when(true, function ($query) {
                $userDatabase = env('DB_DATABASE_USER', '');
                $query->addSelect(DB::raw("CASE LEFT(`shop_flow`.`user_id`, 1)
                WHEN 'v' THEN (SELECT `nickname` FROM {$userDatabase}`wx_user` WHERE `id` = substring(`shop_flow`.`user_id`, 2)) 
                WHEN 'a' THEN (SELECT nickname FROM {$userDatabase}`alipay_user` WHERE `id` = substring(`shop_flow`.`user_id`, 2)) 
                ELSE (SELECT nickname FROM {$userDatabase}`user_info`  WHERE `user_id` = `shop_flow`.`user_id`)
                END as nickname"));
                $query->addSelect(DB::raw("CASE LEFT(`shop_flow`.`user_id`, 1)
                WHEN 'v' THEN '匿名用户无手机号'
                WHEN 'a' THEN '匿名用户无手机号' 
                ELSE (SELECT mobile FROM {$userDatabase}`user`  WHERE `id` = `shop_flow`.`user_id`)
                END as mobile"));
                $query->addSelect(DB::raw("CASE LEFT(`shop_flow`.`user_id`, 1)
                WHEN 'v' THEN ''
                WHEN 'a' THEN '' 
                ELSE (SELECT avatar FROM {$userDatabase}`user_info`  WHERE `user_id` = `shop_flow`.`user_id`)
                END as avatar"));
            })->when($start, function ($query) use ($start) {
                $query->where('shop_flow.create_time', '>=', $start);
            })->when($end, function ($query) use ($end) {
                $query->where('shop_flow.create_time', '<=', $end);
            })->where([
                'shop_flow.shop_id' => $shop_id,
            ])->when($type, function ($query) use ($type) {
                if ($type == 'in') {
                    $query->where('shop_flow.integral', '>=', 0);
                } elseif ($type == 'out') {
                    $query->where('shop_flow.integral', '<', 0);
                }
            });
        $search = $request->input('search', null);
        $base = DB::table(DB::raw(sprintf("(%s) temp", $query->toSql())))
            ->setBindings($query->getBindings())
            ->when($search, function ($query) use ($search) {
                $query->whereRaw('CONCAT(temp.mobile,temp.nickname,temp.order_num) LIKE ?', "%{$search}%");
            });
        $total_integral = $base->sum('temp.integral');
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('temp.id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_FLOW, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $flow = $base->orderBy('temp.id', 'desc')->offset(($page - 1) * $page_size)->limit($page_size)->get();
        return $this->json([
            'page_size'      => (int)$page_size,
            'count'          => (int)$count,
            'total_page'     => (int)$totalPage,
            'current_page'   => (int)$page,
            'total_integral' => $total_integral,
            'flows'          => $flow,
        ]);
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param Request $request
     * @param string $user_id
     * @param string $shop_id
     * @return Response
     */
    public function userTotal(Request $request, $user_id, $shop_id)
    {
        if (is_numeric($user_id)) {
            $user_id = DB::table(env('DB_DATABASE_USER', '') . 'user as user')
                ->select('id as user_id')
                ->where('id', $user_id)
                ->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('v',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'wx_user as v_user')
                        ->where('user_id', $user_id);
                })->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('a',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'alipay_user as a_user')
                        ->where('user_id', $user_id);
                })->distinct()
                ->pluck('user_id')
                ->toArray();
        } else {
            $user_id = explode(',', $user_id);
        }
        $total = DB::table('user')->whereIn('user_id', $user_id)
            ->sum('total_amount');
        $surplus = DB::table('user')
            ->whereIn('user_id', $user_id)
            ->where('shop_id', $shop_id)
            ->sum('total_amount');
        $cumulative = DB::table('user')
            ->whereIn('user_id', $user_id)
            ->where('shop_id', $shop_id)
            ->sum('cumulative');
        return $this->json([
            'total'      => (float)$total,
            'surplus'    => (float)$surplus,
            'cumulative' => $cumulative,
        ]);
    }

    /**
     * @param Request $request
     * @param string $user_id
     * @param int $page
     * @return Response
     * @throws \Exception
     */
    public function userFlow(Request $request, $user_id, $page)
    {
        $this->validate($request, [
            'type'      => 'in:in,out',
            'search'    => 'string|max:100',
            'page_size' => 'integer|min:1',
        ]);
        if (is_numeric($user_id)) {
            $user_id = DB::table(env('DB_DATABASE_USER', '') . 'user as user')
                ->select('id as user_id')
                ->where('id', $user_id)
                ->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('v',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'wx_user as v_user')
                        ->where('user_id', $user_id);
                })->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('a',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'alipay_user as a_user')
                        ->where('user_id', $user_id);
                })->distinct()
                ->pluck('user_id')
                ->toArray();
        } else {
            $user_id = explode(',', $user_id);
        }
        $search = $request->input('search', null);
        $type = $request->input('type', null);
        $base = DB::table('user_flow')
            ->select([
                'user_flow.*'
            ])->whereIn('user_flow.user_id', $user_id)
            ->addSelect(DB::raw('info.name as shop_name, info.logo as shop_logo'))
            ->leftJoin(sprintf('%sshop_info as info', env('DB_DATABASE_USER', '')), 'info.id', '=', 'user_flow.shop_id')
            ->when($search, function ($query) use ($search) {
                $query->whereRaw('CONCAT(info.name,user_flow.order_num) LIKE ?', "%{$search}%");
            })->when($type, function ($query) use ($type) {
                if ($type == 'in') {
                    $query->where('user_flow.integral', '>=', 0);
                } elseif ($type == 'out') {
                    $query->where('user_flow.integral', '<', 0);
                }
            });
        $page_size = $request->input('page_size', $this->page_size);
        $count = $base->count('user_flow.id');
        $totalPage = ceil($count / $page_size);
        if ($totalPage == 0) {
            throw new \Exception(self::INTEGRAL_NOT_FOUND_FLOW, 404);
        }
        $page = ($page <= 1) ? 1 : $page;
        $flow = $base->orderBy('user_flow.id', 'desc')->offset(($page - 1) * $page_size)->limit($page_size)->get();
        return $this->json([
            'page_size'    => (int)$page_size,
            'count'        => (int)$count,
            'total_page'   => (int)$totalPage,
            'current_page' => (int)$page,
            'flows'        => $flow,
        ]);
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param Request $request
     * @return Response
     * @throws \Exception
     */
    public function entrance(Request $request)
    {
        $this->validate($request, [
            'userId'       => 'required|alpha_num',
            'shopId'       => 'required|alpha_num',
            'type'         => 'required|in:line,shade',
            'orderNo'      => 'required|string|min:1|max:255',
            'total'        => 'required|integer|min:0',
            'amount'       => 'required|integer|min:0',
            'integral'     => 'required|integer|min:0',
            'cashRatio'    => 'required|numeric|min:0',
            'inteRatio'    => 'required|numeric|min:0',
            'popularize'   => 'array',
            'distribution' => 'array',
        ]);
        $orderId = DB::table('orders')
            ->where([
                'order_num' => $request->input('orderNo'),
            ])->first(['id']);
        if (!is_null($orderId)) {
            throw new \Exception(self::INTEGRAL_ORDER_ALREADY_FINISH, 202);
        }
        $order = new Order(
            $request->input('type'),
            $request->input('shopId'),
            $request->input('userId'),
            $request->input('orderNo'),
            $request->input('total'),
            $request->input('amount'),
            $request->input('integral'),
            $request->input('cashRatio'),
            $request->input('inteRatio'),
            $request->input('popularize', []),
            $request->input('distribution', [])
        );
        return $this->json($order->account());
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param Request $request
     * @return Response
     */
    public function shopManualTasks(Request $request)
    {
        $this->validate($request, [
            'user_id'    => 'required|array',
            'shop_id'    => 'required|alpha_num',
            'day_return' => 'required|integer|min:1',
            'day_every'  => 'required|integer|min:1',
        ]);
        $ids = array_values(array_filter(array_unique(
            $request->input('user_id')),
            function ($value) {
                return is_numeric($value);
            }));
        $manual = new Manual(
            $request->input('shop_id'),
            $ids,
            $request->input('day_return'),
            $request->input('day_every')
        );
        $manual->run();
        return $this->json('成功');
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param $order_num
     * @return Response
     */
    public function redPack($order_num)
    {
        return $this->json(Cache::get($order_num, null));
    }

    /**
     * @param string $user_id
     * @return Response
     */
    public function redPackByDay($user_id)
    {
        if (is_numeric($user_id)) {
            $user_id = DB::table(env('DB_DATABASE_USER', '') . 'user as user')
                ->select('id as user_id')
                ->where('id', $user_id)
                ->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('v',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'wx_user as v_user')
                        ->where('user_id', $user_id);
                })->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('a',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'alipay_user as a_user')
                        ->where('user_id', $user_id);
                })->distinct()
                ->pluck('user_id')
                ->toArray();
        } else {
            $user_id = explode(',', $user_id);
        }
        $query = DB::table('user_flow')
            ->select([
                'user_flow.shop_id',
                DB::raw("COUNT(user_flow.integral) as count"),
                //DB::raw("GROUP_CONCAT(user_flow.integral) as details"),
                DB::raw("SUM(user_flow.integral) as total"),
            ])
            ->whereIn('user_flow.user_id', $user_id)
            ->where('user_flow.integral', '>', 0)
            ->where([
                'user_flow.read'   => 'false',
                'user_flow.status' => 'success',
            ])->groupBy('user_flow.shop_id');
        $redPack = DB::table(DB::raw(sprintf("(%s) temp", $query->toSql())))
            ->setBindings($query->getBindings())
            ->select(['temp.*',])
            ->addSelect(DB::raw('info.name as shop_name,info.logo as shop_avatar'))
            ->leftJoin(sprintf('%sshop_info as info', env('DB_DATABASE_USER', '')), 'info.id', '=', 'temp.shop_id')
            ->get();
        DB::table('user_flow')
            ->whereIn('user_id', $user_id)
            ->update([
                'read'        => 'true',
                'update_time' => Time::current(),
            ]);
        return $this->json($redPack);
    }

    /*----------------------------------------------------------------------*/
    /**
     * @param string $status
     * @param string $type
     * @param string $shop_id
     * @return Response
     */
    public function shopTasksStatus($status, $type, $shop_id)
    {
        $base = DB::table('shop')
            ->where(['shop_id' => $shop_id,]);
        if ($status === 'start') {
            if ($type === 'day') {
                $base->update([
                    'day_enabled' => 'true',
                    'update_time' => Time::current(),
                ]);
                return $this->json('开启天天红包成功');
            } elseif ($type === 'luck') {
                $base->update([
                    'luck_enabled' => 'true',
                    'update_time'  => Time::current(),
                ]);
                return $this->json('开启幸运红包成功');
            } else {
                $base->update([
                    'mal_enabled' => 'true',
                    'update_time' => Time::current(),
                ]);
                return $this->json('开启手动红包成功');
            }
        } elseif ($status === 'stop') {
            if ($type === 'day') {
                $base->update([
                    'day_enabled' => 'false',
                    'update_time' => Time::current(),
                ]);
                return $this->json('关闭天天红包成功');
            } elseif ($type === 'luck') {
                $base->update([
                    'luck_enabled' => 'false',
                    'update_time'  => Time::current(),
                ]);
                return $this->json('关闭幸运红包成功');
            } else {
                $base->update([
                    'mal_enabled' => 'false',
                    'update_time' => Time::current(),
                ]);
                return $this->json('关闭手动红包成功');
            }
        } else {
            if ($type === 'day') {
                return $this->json($base->value('day_enabled'));
            } elseif ($type === 'luck') {
                return $this->json($base->value('luck_enabled'));
            } else {
                return $this->json($base->value('mal_enabled'));
            }
        }
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param string $status
     * @param string $type
     * @param string $shop_id
     * @param string $user_id
     * @return Response
     */
    public function userTasksStatus($status, $type, $shop_id, $user_id)
    {
        if (is_numeric($user_id)) {
            $user_id = DB::table(env('DB_DATABASE_USER', '') . 'user as user')
                ->select('id as user_id')
                ->where('id', $user_id)
                ->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('v',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'wx_user as v_user')
                        ->where('user_id', $user_id);
                })->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('a',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'alipay_user as a_user')
                        ->where('user_id', $user_id);
                })->distinct()
                ->pluck('user_id')
                ->toArray();
        } else {
            $user_id = explode(',', $user_id);
        }
        $base = DB::table('user')
            ->whereIn('user_id', $user_id)
            ->where('shop_id', $shop_id);
        if ($status === 'start') {
            if ($type === 'day') {
                $base->update([
                    'day_enabled' => 'true',
                    'update_time' => Time::current(),
                ]);
                return $this->json('开启天天积分红包成功');
            } else if ($type === 'luck') {
                $base->update([
                    'luck_enabled' => 'true',
                    'update_time'  => Time::current(),
                ]);
                return $this->json('开启幸运积分红包成功');
            } else {
                $base->update([
                    'mal_enabled' => 'true',
                    'update_time' => Time::current(),
                ]);
                return $this->json('开启手动积分红包成功');
            }
        } else {
            if ($type === 'day') {
                $base->update([
                    'day_enabled' => 'false',
                    'update_time' => Time::current(),
                ]);
                return $this->json('关闭天天积分红包成功');
            } else if ($type === 'luck') {
                $base->update([
                    'luck_enabled' => 'false',
                    'update_time'  => Time::current(),
                ]);
                return $this->json('关闭幸运积分红包成功');
            } else {
                $base->update([
                    'mal_enabled' => 'false',
                    'update_time' => Time::current(),
                ]);
                return $this->json('关闭手动积分红包成功');
            }
        }
    }

    /**
     * @param string $type
     * @param string $shop_id
     * @param string $user_id
     * @return Response
     */
    public function clearUserTasks($type, $shop_id, $user_id)
    {
        if (is_numeric($user_id)) {
            $user_id = DB::table(env('DB_DATABASE_USER', '') . 'user as user')
                ->select('id as user_id')
                ->where('id', $user_id)
                ->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('v',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'wx_user as v_user')
                        ->where('user_id', $user_id);
                })->union(function ($query) use ($user_id) {
                    $query->select(DB::raw("CONCAT('a',id) as user_id"))
                        ->from(env('DB_DATABASE_USER', '') . 'alipay_user as a_user')
                        ->where('user_id', $user_id);
                })->distinct()
                ->pluck('user_id')
                ->toArray();
        } else {
            $user_id = explode(',', $user_id);
        }
        $base = DB::table('task')
            ->whereIn('user_id', $user_id)
            ->where('shop_id', $shop_id);
        if ($type === 'day') {
            $base->where('type', 'day')
                ->update([
                    'enabled'     => 'false',
                    'count'       => 0,
                    'update_time' => Time::current(),
                ]);
            return $this->json('清空天天积分红包成功');
        } elseif ($type === 'luck') {
            $base->where('type', 'luck')
                ->update([
                    'enabled'     => 'false',
                    'count'       => 0,
                    'update_time' => Time::current(),
                ]);
            return $this->json('清空幸运积分红包成功');
        } else {
            $base->where('type', 'manual')
                ->update([
                    'enabled'     => 'false',
                    'count'       => 0,
                    'update_time' => Time::current(),
                ]);
            return $this->json('清空手动积分红包成功');
        }
    }

    /**
     * @param string $status start|stop
     * @param string $task_ids
     * @return Response
     */
    public function taskStatus($status, $task_ids)
    {
        $ids = array_values(array_filter(array_unique(explode(',', $task_ids)), function ($value) {
            return is_numeric($value);
        }));
        if ($status === 'start') {
            DB::table('task')
                ->whereIn('id', $ids)
                ->update([
                    'enabled'     => 'true',
                    'update_time' => Time::current(),
                ]);
            return $this->json('开启成功');
        } else {
            DB::table('task')
                ->whereIn('id', $ids)
                ->update([
                    'enabled'     => 'false',
                    'update_time' => Time::current(),
                ]);
            return $this->json('关闭成功');
        }
    }

    /**
     * @param string $task_ids
     * @return Response
     */
    public function clearTasks($task_ids)
    {
        $ids = array_values(array_filter(array_unique(explode(',', $task_ids)), function ($value) {
            return is_numeric($value);
        }));
        DB::table('task')
            ->whereIn('id', $ids)
            ->update([
                'enabled'     => 'false',
                'count'       => 0,
                'update_time' => Time::current(),
            ]);
        return $this->json('清空成功');
    }

    /*----------------------------------------------------------------------*/

    /**
     * @param Request $request
     * @return Response
     * @throws \Throwable
     */
    public function pay(Request $request)
    {
        $this->validate($request, [
            'userId'   => 'required|string',
            'shopId'   => 'required|alpha_num',
            'orderNo'  => 'required|string|min:1|max:255',
            'integral' => 'required|numeric|min:0',
        ]);
        $userId = $request->input('userId');
        $shopId = $request->input('shopId');
        $orderNum = $request->input('orderNo');
        $integral = $request->input('integral');
        $flow = DB::table('user_flow')
            ->where([
                'order_num' => $orderNum,
            ])->first(['id']);
        if (!is_null($flow)) {
            throw new \Exception(self::INTEGRAL_ORDER_ALREADY_FINISH, 202);
        }
        DB::beginTransaction();
        try {
            if (is_numeric($userId)) {
                $user_id = DB::table(env('DB_DATABASE_USER', '') . 'user as user')
                    ->select('id as user_id')
                    ->where('id', $userId)
                    ->union(function ($query) use ($userId) {
                        $query->select(DB::raw("CONCAT('v',id) as user_id"))
                            ->from(env('DB_DATABASE_USER', '') . 'wx_user as v_user')
                            ->where('user_id', $userId);
                    })->union(function ($query) use ($userId) {
                        $query->select(DB::raw("CONCAT('a',id) as user_id"))
                            ->from(env('DB_DATABASE_USER', '') . 'alipay_user as a_user')
                            ->where('user_id', $userId);
                    })->distinct()
                    ->pluck('user_id')
                    ->toArray();
            } else {
                $user_id = explode(',', $userId);
            }
            $users = DB::table('user')
                ->whereIn('user_id', $user_id)
                ->where('shop_id', $shopId)
                ->where('total_amount', '<>', 0)
                ->lockForUpdate()
                ->get();
            $total_amount = $users->sum(function ($value) {
                return $value->total_amount;
            });
            if ($total_amount < $integral) {
                throw new \Exception(self::INTEGRAL_NOT_ENOUGH, 400);
            }
            foreach ($users as $user) {
                if ($integral <= 0) {
                    break;
                } else {
                    if ($user->total_amount >= $integral) {
                        DB::table('user')
                            ->where([
                                'user_id' => $user->user_id,
                                'shop_id' => $shopId,
                            ])->update([
                                'total_amount' => $user->total_amount - $integral,
                                'update_time'  => Time::current(),
                            ]);
                        DB::table('user_flow')
                            ->insert([
                                'user_id'     => $user->user_id,
                                'shop_id'     => $shopId,
                                'order_num'   => $orderNum,
                                'status'      => 'success',
                                'reason'      => 'success',
                                'info'        => '积分消费',
                                'refund'      => 'true',
                                'integral'    => -$integral,
                                'should'      => -$integral,
                                'platform'    => 0,
                                'history'     => $user->total_amount - $integral,
                                'task_id'     => 0,
                                'create_time' => Time::current(),
                                'update_time' => Time::current(),
                            ]);
                        break;
                    } else {
                        DB::table('user')
                            ->where([
                                'user_id' => $user->user_id,
                                'shop_id' => $shopId,
                            ])->update([
                                'total_amount' => 0,
                                'update_time'  => Time::current(),
                            ]);
                        DB::table('user_flow')
                            ->insert([
                                'user_id'     => $user->user_id,
                                'shop_id'     => $shopId,
                                'order_num'   => $orderNum,
                                'status'      => 'success',
                                'reason'      => 'success',
                                'info'        => '积分消费',
                                'refund'      => 'true',
                                'integral'    => -$user->total_amount,
                                'should'      => -$user->total_amount,
                                'platform'    => 0,
                                'history'     => 0,
                                'task_id'     => 0,
                                'create_time' => Time::current(),
                                'update_time' => Time::current(),
                            ]);
                        $integral -= $user->total_amount;
                    }
                }
            }
            DB::commit();
            return $this->json(null);
        } catch (\Throwable $throwable) {
            DB::rollBack();
            throw $throwable;
        }
    }

    /**
     * @param Request $request
     * @return Response
     * @throws \Throwable
     */
    public function rollback(Request $request)
    {
        $this->validate($request, [
            'orderNo' => 'required|string|min:1|max:255',
        ]);
        $orderNum = $request->input('orderNo');
        DB::beginTransaction();
        try {
            $flows = DB::table('user_flow')
                ->where([
                    'order_num' => $orderNum,
                ])->lockForUpdate()
                ->get();
            if ($flows->isEmpty()) {
                throw new \Exception(self::INTEGRAL_NOT_FOUND_FLOW, 404);
            }
            foreach ($flows as $flow) {
                if ($flow->refund != 'true') {
                    throw new \Exception(self::INTEGRAL_NOT_ALLOW_REFUND, 400);
                }
                $total_amount = DB::table('user')
                    ->where([
                        'user_id' => $flow->user_id,
                        'shop_id' => $flow->shop_id,
                    ])->lockForUpdate()
                    ->value('total_amount');
                DB::table('user_flow')
                    ->where([
                        'order_num' => $orderNum,
                    ])->update([
                        'refund'      => 'false',
                        'update_time' => Time::current(),
                    ]);
                DB::table('user')
                    ->where([
                        'user_id' => $flow->user_id,
                        'shop_id' => $flow->shop_id,
                    ])->update([
                        'total_amount' => $total_amount + abs($flow->integral),
                        'update_time'  => Time::current(),
                    ]);
                DB::table('user_flow')
                    ->insert([
                        'user_id'     => $flow->user_id,
                        'shop_id'     => $flow->shop_id,
                        'order_num'   => $flow->order_num,
                        'status'      => 'success',
                        'reason'      => 'success',
                        'info'        => '积分消费失败退还',
                        'refund'      => 'false',
                        'integral'    => abs($flow->integral),
                        'should'      => abs($flow->integral),
                        'platform'    => 0,
                        'history'     => $total_amount + abs($flow->integral),
                        'task_id'     => 0,
                        'create_time' => Time::current(),
                        'update_time' => Time::current(),
                    ]);
            }
            DB::commit();
            return $this->json(null);
        } catch (\Throwable $throwable) {
            DB::rollBack();
            throw $throwable;
        }
    }
}
